const fs = require('fs');
const _ = require("lodash");
const xlsx = require("xlsx");
function process_RS(stream/*:ReadStream*/, cb/*:(wb:Workbook)=>void*/)/*:void*/{
    var buffers = [];
    stream.on('data', function(data) { buffers.push(data); });
    stream.on('end', function() {
        var buffer = Buffer.concat(buffers);
        // buffer = iconv.decode(buffer,'gbk');
        var workbook = xlsx.read(buffer, {type:"buffer"});

        /* DO SOMETHING WITH workbook IN THE CALLBACK */
        cb(workbook);
    });
}
function addRecordToResults(result, record){
    result[record.sub_port] = record;
}

// 先读入平台所有子端口，作为基础数据库
let filePath = './db.xlsx';
    let inputPath = './子端口补录模板(以主端口号命名)-骉云(9)_5669.xlsm';
let rs = fs.createReadStream(filePath);

process_RS(rs, async wb => {
    let first_sheet_name = wb.SheetNames[0];
    let ws = wb.Sheets[first_sheet_name];

    let result = {};
    let block = {
        row: 2,
        value: {}
    };

    for (let meta in ws) {
        if (meta[0] === '!') continue;
        let location = meta.match(/^([A-Z])(.*)$/);
        if (location) {
            let col = location[1];
            let row = +location[2];
            let value = ws[meta].w.trim();

            if (row === 1) {
                block[col] =value;
            } else {
                if (row > block.row) {
                    addRecordToResults(result, block.value);
                    block.value = {}
                }
                block.row = row;
                block.value[block[col]] = value;
            }
        }
    }
    addRecordToResults(result, block.value);
    await doSomething(result);
});
// 再读入待填充的补录文件作为输入数据
function doSomething(db){
    let rs2 = fs.createReadStream(inputPath);
    process_RS(rs2, async wb => {
        let first_sheet_name = wb.SheetNames[0];
        let ws = wb.Sheets[first_sheet_name];

        let result = [];
        let block = {
            row: 2,
            value: {}
        };
try{
    for (let meta in ws) {
        if (meta[0] === '!') continue;
        let location = meta.match(/^([A-Z])(.*)$/);
        if (location) {
            let col = location[1];
            let row = +location[2];
            let value = ws[meta].w.trim();
// console.log(location, row, value)
            if (row === 1) {
                block[col] =value;
            } else {
                if (row > block.row) {
                    matchDB(result, block, db);
                    block.value = {}
                }
                block.row = row;
                block.value[block[col]] = value;
            }
        }
    }
    matchDB(result, block, db);
}catch(err){
    console.log(err);
}

        // console.log(result)
        let xlsData = [{
            sheet : 'sheet1',
            title : [
                {key : '操作类型', name  : '操作类型'},
                {key : '短信子端口号', name  : '短信子端口号'},
                {key : '短信主端口号', name  : '短信主端口号'},
                {key : '接入省', name  : '接入省'},
                {key : '接入地市', name  : '接入地市'},
                {key : '短信签名', name  : '短信签名'},
                {key : 'acount', name  : '账号'}
            ],
            data : result
        }];

        let buffer = require('../xlsUtils').buildExcelResultBuf(xlsData);
        fs.writeFileSync('output.xlsx', buffer);
        console.log('文档以输出，请查阅。');
    })
    // console.log(db)
}
let i = 0;
// 输入数据逐条去匹配基础数据库，产生最终的输出文件，xls格式
function matchDB(result, b, db){
    i++;
try{
    // console.log(b.value)
    let sub = b.value['短信子端口号'];
    let info = db[sub];
    if(info){
        let signResult = info.sign && info.sign.match(/【(.*)】/)
        b.value['短信签名'] = signResult && signResult[1];
        b.value['acount'] = info.account;
    }else{
        console.log(sub)
    }
}catch(err){
    console.log(err)
}


    result.push(b.value)
}
